const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");
// 供应商对账单
router.post('/report/gysdzd', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let salesId = formData.salesId?formData.salesId:0;
    let excel = body.excel
    let sqls = `
        with bills as (
            select isInit, tenantId, buId, buName,   transTypeName, billDate, billNo,
                   0 totalAmountReal,0 disAmountReal,0 rpAmountReal,0 ysAmountReal,0 arrearsReal,0 sjsk,
                   initYe ye,'' salesName,'' remark ,0 hxAmount ,'' billType, 0 transType
            from (
                     select 1 isInit, tenantId, buId, buName, sum(initYe) initYe,N'期初余额' transTypeName,'' billDate,'' billNo 
                     from (
                              select tenantId,buId,buName,
                                     iif(billType='PAYMENT',0,arrearsReal)-iif(billType='PAYMENT',totalAmount,0) initYe
                              from v_main where tenantId=${tenantId} and billType in('PUR','YF','PAYMENT') and billState='accounted' and billDate<'${beginDate}' 
                                          and (${buId}=0 or buId=${buId}) and (${salesId}=0 or salesId=${salesId})
                              union all
                              select tenantId,buId,buName,
                                     iif(price2<>0,price2,0)-iif(price1<>0,price1,0) initYe
                              from v_card_info where tenantId=${tenantId} and billState='accounted'
                                                 and subjectId in (select id from category where tenantId=${tenantId} and name in(N'应付账款',N'其他应付款'))
                                                 and byIid=0 and billDate<'${beginDate}'
                                                 and (${buId}=0 or buId=${buId})
                          ) a group by tenantId,buId,buName
                 ) a
            union all
            select 0 isInit, tenantId, buId, buName,transTypeName, billDate,billNo,
                   totalAmountReal,disAmountReal,rpAmountReal,ysAmountReal,arrearsReal,sjsk,
                   arrearsReal-sjsk ye
                   ,salesName,remark ,hxAmount ,billType,transType
            from (
                     select tenantId,buId,buName,transTypeName,billDate,billNo,billType,
                            case when billType='PAYMENT' then 0 else totalAmountReal end totalAmountReal, 
                            iif(billType in('PAYMENT','ZC'),0,disAmountReal) disAmountReal, 
                            iif(billType in('PAYMENT','ZC'),0,rpAmountReal) rpAmountReal, 
                            iif(billType in('PAYMENT','ZC'),0,ysAmountReal) ysAmountReal,
                            case when billType='PAYMENT' then 0 else arrearsReal end arrearsReal,
                            iif(billType in('PAYMENT','ZC'),totalAmount,0) sjsk, 
                            salesName,remark,hxAmount  ,transType
                     from v_main_withhx where tenantId=${tenantId} and billType in('PUR','YF','PAYMENT','ZC') and billState='accounted' and billDate between '${beginDate}' and '${endDate}'
                                   and (${buId}=0 or buId=${buId}) and (${salesId}=0 or salesId=${salesId})
                     union all
                     select tenantId,buId,buName,N'凭证' transTypeName,billDate,billNo,'PZ' billType ,
                            price2 totalAmountReal,
                            0 disAmountReal,
                            0 rpAmountReal,
                            0 ysAmountReal,
                            price2 arrearsReal,
                            price1 sjsk,
                            '' salesName ,remark, 0 hxAmount ,190001 transType
                     from v_card_info where tenantId=${tenantId} and billState='accounted'
                                        and subjectId in (select id from category where tenantId=${tenantId} and name in(N'应付账款',N'其他应付款'))
                                        and byIid=0 and billDate between '${beginDate}' and '${endDate}'
                                        and (${buId}=0 or buId=${buId})
                 ) a
        ) select isInit, tenantId, buId, buName, transTypeName, billDate, billNo,
                 totalAmountReal,disAmountReal,rpAmountReal,ysAmountReal,arrearsReal,sjsk sk,
                 ye,salesName,remark,hxAmount,billType,arrearsReal-hxAmount leftYe from bills order by buId,isInit desc ,billDate,transType
    `
    let list = await db.query(sqls, {type: QueryTypes.SELECT})
    if(excel) {
        return tool.doExcelOut(ctx, list)
    }
    else {
        let ret = []
        let gp = tool.xe.groupBy(list, 'buId')
        for(let i in gp) {
            if(gp[i][0].transTypeName !== '期初余额') {
                ret.push({
                    buId: gp[i][0].buId,
                    buName: gp[i][0].buName,
                    transTypeName: '期初余额',
                    ys: 0,
                    ye: 0
                })
            }
            for (let j = 0; j < gp[i].length; j++) {
                let row = gp[i][j]
                if(j!== 0) {
                    row.buName = ''
                }
                if(row.billType !== 'PUR') {
                    row.leftYe = 0
                }
                ret.push(row)
            }
            ret.push({
                buId: gp[i][0].buId,
                buName: '',
                transTypeName: '小计',
                ys: tool.xe.sum(gp[i],'ys'),
                ye: 0
            })
        }
        for (let i = 0; i < ret.length; i++) {
            if(ret[i].transTypeName !== '期初余额') {
                if(ret[i].transTypeName === '小计') {
                    ret[i].ye = ret[i-1].ye
                }
                else {
                    ret[i].ye = tool.add(ret[i-1].ye, ret[i].ye)
                }
            }
        }
        return ctx.suc('', {
            total: 0,
            list: ret
        })
    }
})





module.exports = router;
